/**
 * 
 */
package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import connection.ConnectionManager;

import domain.ScoredSeen;

public class JDBCScoredSeenDAO implements IScoredSeenDAO {

	Connection conn;
	
	/* (non-Javadoc)
	 * @see dao.IScoredSeenDAO#getAllScoredSeenByUser(java.lang.Integer)
	 */
	public List<ScoredSeen> getAllScoredSeenByUser(Integer idUser) {
		List<ScoredSeen> scoredSeens = new LinkedList<ScoredSeen>();
		Double averageScore=0.0;;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT video_vid, usuarios_uid, puntuacion, visto " +
					"FROM video_usuarios WHERE usuarios_uid ="+idUser+";");
			ScoredSeen scoredSeen = null;
			while(rs.next()){
				averageScore=getAverageScored(rs.getInt(2), rs.getInt(1));
				scoredSeen = new ScoredSeen(rs.getInt(1), rs.getInt(2), rs.getInt(3), averageScore, rs.getInt(4));
				scoredSeens.add(scoredSeen);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return scoredSeens;
	}

	/* (non-Javadoc)
	 * @see dao.IScoredSeenDAO#changeScore(domain.ScoredSeen)
	 */
	public void changeScore(ScoredSeen scoredSeen) {
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE video_usuarios SET puntuacion="+scoredSeen.getScore()+
					" WHERE video_vid='"+scoredSeen.getIdVideo()+"' and usuarios_uid='"+scoredSeen.getIdUser()+"';");
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see dao.IScoredSeenDAO#changeSeen(domain.ScoredSeen)
	 */
	public void changeSeen(ScoredSeen scoredSeen) {
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE video_usuarios SET visto="+scoredSeen.isSeen()+
					" WHERE video_vid="+scoredSeen.getIdVideo()+" and usuarios_uid="+scoredSeen.getIdUser());
		
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see dao.IScoredSeenDAO#getAverageScored(java.lang.Integer, java.lang.Integer)
	 */
	@Override
	public Double getAverageScored(Integer idUser, Integer idVideo) {
		Double averageScore=0.0;;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT avg(puntuacion) FROM video_usuarios " +
					"WHERE video_vid='"+idVideo+"' AND (usuarios_uid IN (SELECT usuarios_uid1 " +
							"FROM usuarios_has_usuarios WHERE usuarios_uid='"+idUser+"')" +
									" OR usuarios_uid='"+idUser+"')");
			if(rs.next()){
				averageScore=rs.getDouble(1);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return averageScore;
	}
	
	/* (non-Javadoc)
	 * @see dao.IScoredSeenDAO#changeSeen(domain.ScoredSeen)
	 */
	public void insertVideoUsuario(Integer idUser, Integer idVideo) {
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("INSERT video_usuarios(video_vid,usuarios_uid,fecha) VALUES('"+idVideo+"','"+idUser+"',SYSDATE());");
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public ScoredSeen getScoredSeenByVideoAndUSer(Integer idVideo, Integer idUser) {
		ScoredSeen scoredSeen = null;
		Double averageScored;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM video_usuarios " +
					"WHERE video_vid='"+idVideo+"' AND usuarios_uid='"+idUser+"';");
			averageScored=getAverageScored(idUser, idVideo);
			if(rs.next()){
				scoredSeen = new ScoredSeen(rs.getInt(1), rs.getInt(2), rs.getInt(3), averageScored, rs.getInt(4));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return scoredSeen;
	}

}
